﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LGPeWJNM.DataAccessLayer
{
    public class InstanciaExcelGlp : Instancia
    {
        public InstanciaExcelGlp(string path)
        {
            var connString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=NO\";", path);
            using (var conn = new System.Data.OleDb.OleDbConnection(connString))
            using (var cmd = new System.Data.OleDb.OleDbCommand())
            {
                conn.Open();
                var oleDbTables = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                var sheets = from System.Data.DataRow row in oleDbTables.Rows
                             select (string)row["TABLE_NAME"];
                cmd.Connection = conn;
                cmd.CommandText = string.Format("SELECT * FROM [{0}]", sheets.First());
                using (var reader = cmd.ExecuteReader())
                {
                    var dests = new List<LGPeWJNM.BaseClasses.ICliente>();
                    this.Destinos = dests;
                    int i = 0;
                    while (reader.Read())
                    {
                        if (reader.GetValue(6) is double)
                        {
                            dests.Add(new LGPeWJNM.BaseClasses.Cliente()
                            {
                                Demanda = Convert.ToInt32(reader.GetDouble(6)),
                                Nome = reader.GetString(0),
                                Id = i++,
                                Coordenada = new BaseClasses.Point()
                                {
                                    Y = reader.GetDouble(7),
                                    X = reader.GetDouble(8)
                                }
                            });
                        }
                    }
                }
            }
        }
    }
}